from vcr.model import Customer, Project, Activity, Registration
from sqlalchemy import func
from datetime import timedelta


def customers(orm):
    return [(x.id, x.name) for x in orm.query(Customer).all()]
    
def projects(orm):
    return [(x.id, str(x)) for x in orm.query(Project).all()]

def activities(orm):
    return [(x.id, x.name) for x in orm.query(Activity).all()]

def registrations(orm, date):
    return [(r.project.name, r.activity.name, r.duration) for r in orm.query(Registration).filter(Registration.when == date)]

def day_total(orm, date):
    v = orm.query(func.sum(Registration.duration)).filter(Registration.when == date)[0][0]
    if v is None:
        return 0
    return v

def week_total(orm, date):
    """ week starts at monday """
    start = date - timedelta(date.weekday())
    end = start + timedelta(7)
    v = orm.query(func.sum(Registration.duration)).filter(Registration.when >= start).filter(Registration.when < end)[0][0]
    if v is None:
        return 0
    return v
